<?xml version="1.0" encoding="UTF-8" standalone='no'?>
<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">

    <changeSet author="vsurubkov" id="29-12-2023-add-table-meaning-types">
        <preConditions onFail="MARK_RAN">
            <not>
                <tableExists tableName="meaning_types"/>
            </not>
        </preConditions>

        <createTable tableName="meaning_types">
            <column name="id_type" type="bigint" autoIncrement="true">
                <constraints primaryKey="true" primaryKeyName="pk_meaning_type"/>
            </column>
            <column name="type" type="nvarchar(256)">
                <constraints nullable="false" unique="true"/>
            </column>
        </createTable>
    </changeSet>

    <changeSet author="vsurubkov" id="29-12-2023-add-table-meanings">
        <preConditions onFail="MARK_RAN">
            <not>
                <tableExists tableName="meanings"/>
            </not>
        </preConditions>

        <createTable tableName="meanings">
            <column name="id_meaning" type="bigint" autoIncrement="true">
                <constraints primaryKey="true" primaryKeyName="pk_meaning"/>
            </column>
            <column name="id_type" type="bigint">
                <constraints nullable="false"/>
            </column>
            <column name="meaning" type="nvarchar(256)">
                <constraints nullable="false"/>
            </column>
        </createTable>
    </changeSet>

    <changeSet author="vsurubkov" id="29-12-2023-add-foreign-key-meaning-types-in-meaning">
        <preConditions onFail="MARK_RAN">
            <not>
                <foreignKeyConstraintExists foreignKeyName="fk_meaning_types_in_meanings"/>
            </not>
        </preConditions>

        <addForeignKeyConstraint constraintName="fk_meaning_types_in_meanings"
                                 baseTableName="meanings"
                                 baseColumnNames="id_type"
                                 referencedTableName="meaning_types"
                                 referencedColumnNames="id_type"
        />
    </changeSet>

    <changeSet author="vsurubkov" id="29-12-2023-add-table-sorts">
        <preConditions onFail="MARK_RAN">
            <not>
                <tableExists tableName="sorts"/>
            </not>
        </preConditions>

        <createTable tableName="sorts">
            <column name="id_sort" type="bigint" autoIncrement="true">
                <constraints primaryKey="true" primaryKeyName="pk_sort"/>
            </column>
            <column name="id_hyperonim" type="bigint">
                <constraints nullable="true"/>
            </column>
            <column name="sort" type="nvarchar(256)">
                <constraints nullable="false" unique="true"/>
            </column>
        </createTable>
    </changeSet>

    <changeSet author="vsurubkov" id="29-12-2023-add-foreign-key-sorts-in-sorts">
        <preConditions onFail="MARK_RAN">
            <not>
                <foreignKeyConstraintExists foreignKeyName="fk_sorts_in_sorts"/>
            </not>
        </preConditions>

        <addForeignKeyConstraint constraintName="fk_sorts_in_sorts"
                                 baseTableName="sorts"
                                 baseColumnNames="id_hyperonim"
                                 referencedTableName="sorts"
                                 referencedColumnNames="id_sort"
        />
    </changeSet>



    <changeSet author="vsurubkov" id="27-02-2024-add-table-meanings_sorts">
        <preConditions onFail="MARK_RAN">
            <not>
                <tableExists tableName="meanings_sorts"/>
            </not>
        </preConditions>

        <createTable tableName="meanings_sorts">
            <column name="id_meaning" type="bigint">
                <constraints nullable="false"/>
            </column>
            <column name="id_sort" type="bigint">
                <constraints nullable="false"/>
            </column>
        </createTable>
    </changeSet>

    <changeSet author="vsurubkov" id="27-02-2024-add-foreign-key-sorts-in-meanings-sorts">
        <preConditions onFail="MARK_RAN">
            <not>
                <foreignKeyConstraintExists foreignKeyName="fk_sorts_in_meanings_sorts"/>
            </not>
        </preConditions>

        <addForeignKeyConstraint constraintName="fk_sorts_in_meanings_sorts"
                                 baseTableName="meanings_sorts"
                                 baseColumnNames="id_sort"
                                 referencedTableName="sorts"
                                 referencedColumnNames="id_sort"
        />
    </changeSet>

    <changeSet author="vsurubkov" id="27-02-2024-add-foreign-key-meaning-in-meanings-sorts">
        <preConditions onFail="MARK_RAN">
            <not>
                <foreignKeyConstraintExists foreignKeyName="fk_meaning_in_meanings_sorts"/>
            </not>
        </preConditions>

        <addForeignKeyConstraint constraintName="fk_meaning_in_meanings_sorts"
                                 baseTableName="meanings_sorts"
                                 baseColumnNames="id_meaning"
                                 referencedTableName="meanings"
                                 referencedColumnNames="id_meaning"
        />
    </changeSet>

    <changeSet author="vsurubkov" id="29-12-2023-add-table-term-meanings">
        <preConditions onFail="MARK_RAN">
            <not>
                <tableExists tableName="term_meanings"/>
            </not>
        </preConditions>

        <createTable tableName="term_meanings">
            <column name="id_term_meaning" type="bigint" autoIncrement="true">
                <constraints primaryKey="true" primaryKeyName="pk_term_meaning"/>
            </column>
            <column name="id_term" type="bigint">
                <constraints nullable="false"/>
            </column>
            <column name="id_meaning" type="bigint">
                <constraints nullable="false"/>
            </column>
            <column name="comment" type="nvarchar(256)">
                <constraints nullable="false"/>
            </column>
        </createTable>
    </changeSet>

    <changeSet author="vsurubkov" id="27-02-2024-add-foreign-key-meaning-in-term-meanings">
        <preConditions onFail="MARK_RAN">
            <not>
                <foreignKeyConstraintExists foreignKeyName="fk_meaning_in_term_meanings"/>
            </not>
        </preConditions>

        <addForeignKeyConstraint constraintName="fk_meaning_in_term_meanings"
                                 baseTableName="term_meanings"
                                 baseColumnNames="id_meaning"
                                 referencedTableName="meanings"
                                 referencedColumnNames="id_meaning"
        />
    </changeSet>

    <changeSet author="vsurubkov" id="27-02-2024-add-foreign-key-term-in-term-meanings">
        <preConditions onFail="MARK_RAN">
            <not>
                <foreignKeyConstraintExists foreignKeyName="fk_term_in_term_meanings"/>
            </not>
        </preConditions>

        <addForeignKeyConstraint constraintName="fk_term_in_term_meanings"
                                 baseTableName="term_meanings"
                                 baseColumnNames="id_term"
                                 referencedTableName="terms"
                                 referencedColumnNames="id_term"
        />
    </changeSet>
</databaseChangeLog>